package light.head.dao.el;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Service;

import light.head.constant.Parm;
import light.head.constant.Sys;
import light.head.dto.ClassesDTO;
import light.head.enm.RESULT;
import light.head.framework.base.BaseDAO;
import light.head.framework.entity.PageBean;
import light.head.model.el.Classes;
@Service
public class ClassesDAO extends BaseDAO<Classes, Integer>{
	public RESULT findByMap(List<Integer> ocs, Map<String, String> map,final PageBean pageBean){
		if(ocs == null) return null;
		List<Object> list = new ArrayList<Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT c.code, c.name,c.ct , c.cu,ui.realName, ");
		sb.append("c.startSignTime, c.finishTime, c.beginTime,c.endSignTime,c.how, c.isFront, c.num, c.oc, c.deleted, count(ucr.uc) FROM Classes c ");
		sb.append("LEFT JOIN UserClassesRelation ucr ON ucr.clc = c.code AND ucr.status = 1 ");
		sb.append("JOIN UserInfo ui ON ui.code = c.cu ");
		sb.append("WHERE ui.deleted = 0 AND c.oc IN (");
		for (Integer i : ocs) {
			sb.append("?,");
			list.add(i);
		}
		sb.deleteCharAt(sb.length() - 1).append(")");
		if(map != null){
			String name = map.get(Parm.NAME);
			String deleted = map.get(Parm.DELETED);
			if(isNotNull(name)){
				sb.append(" AND c.name LIKE ?");
				list.add("%" + name + "%");
			}
			if(isNotNull(deleted)){
				sb.append(" AND c.deleted = ? ");
				list.add(Boolean.parseBoolean(deleted));
			}
		}
		sb.append(" GROUP BY c.code ORDER BY c.code DESC");
		final String sql = sb.toString();
		sb.replace(sb.indexOf("GROUP"),sb.length()," ");
		final String countSql = sb.replace(7, sb.indexOf("FROM"), "COUNT(*) ").toString();
		final List<Object> parmList = list;
		return this.getHibernateTemplate().execute(new HibernateCallback<RESULT>() {

			@SuppressWarnings("unchecked")
			@Override
			public RESULT doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				for (int i = 0; i < parmList.size(); i++) {
					query.setParameter(i, parmList.get(i));
				}
				query.setFirstResult((pageBean.getPage() - 1) * pageBean.getPageSize());
				query.setMaxResults(pageBean.getPageSize());
				
				List<Object[]> resultSet = query.list();
				if (resultSet != null) {
					List<ClassesDTO> ssList = new ArrayList<ClassesDTO>();
					ClassesDTO c = null;
					for (Object[] row : resultSet) {
						c = new ClassesDTO();
						Integer code = row[0] != null ? (Integer)row[0] : null;
						String name = row[1] != null ? String.valueOf(row[1]) :null;
						Date ct = row[2] != null ? (Date)row[2] : null;
						Integer cu = row[3] != null ? (Integer)row[3] :null;
						String cn = row[4] != null ? String.valueOf(row[4]): null;
						Date startSignTime = row[5] != null ? (Date)row[5] :null;
						Date finishTime = row[6] != null ? (Date)row[6] : null;
						Date beginTime = row[7] != null ? (Date)row[7] : null;
						Date endSignTime = row[8] != null ? (Date)row[8] : null;
						Byte how = row[9] != null ? (Byte)row[9]:null;
						Boolean isFront = row[10] != null ? (Boolean)row[10] :null;
						Short num = row[11] != null ? (Short)row[11] :null;
						Integer coc = row[12] != null ?(Integer)row[12] : null;
						Boolean deleted = row[13] != null ? (Boolean)row[13] : null;
						Integer count = row[14] != null ?((BigInteger)row[14]).intValue() : null;
						
						c.setCode(code);
						c.setName(name);
						c.setCt(ct);
						c.setCu(cu);
						c.setCn(cn);
						c.setStartSignTime(startSignTime);
						c.setFinishTime(finishTime);
						c.setBeginTime(beginTime);
						c.setEndSignTime(endSignTime);
						c.setHow(how);
						c.setIsFront(isFront);
						c.setNum(num);
						c.setOc(coc);
						c.setDeleted(deleted);
						c.setCount(count);
						ssList.add(c);
					}
					pageBean.setResultList(ssList);
					SQLQuery countQuery = session.createSQLQuery(countSql);
					for (int i = 0; i < parmList.size(); i++) {
						countQuery.setParameter(i, parmList.get(i));
					}
					
					Long total = ((BigInteger) countQuery.uniqueResult()).longValue();
					pageBean.setTotal(total);
					pageBean.compute();
					return RESULT.SUCCESS;
				}
				return RESULT.ERROR;
			}
		});
	}
	public RESULT findByMap2(Integer oc, Map<String, String> map,final PageBean pageBean){
		if(oc == null) return null;
		List<Object> list = new ArrayList<Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT c.code, c.name,c.ct , c.cu,ui.realName, ");
		sb.append("c.startSignTime, c.finishTime, c.beginTime,c.endSignTime,c.how, c.isFront, c.num, c.oc, c.deleted, count(ucr.uc) FROM Classes c ");
		sb.append("LEFT JOIN UserClassesRelation ucr ON ucr.clc = c.code AND ucr.status = 1 ");
		sb.append("JOIN UserInfo ui ON ui.code = c.cu ");
		sb.append("WHERE ui.deleted = 0 AND c.oc =?");
		list.add(oc);
		if(map != null){
			String name = map.get(Parm.NAME);
			String deleted = map.get(Parm.DELETED);
			if(isNotNull(name)){
				sb.append(" AND c.name LIKE ?");
				list.add("%" + name + "%");
			}
			if(isNotNull(deleted)){
				sb.append(" AND c.deleted = ? ");
				list.add(Boolean.parseBoolean(deleted));
			}
		}
		sb.append(" GROUP BY c.code ORDER BY c.code DESC");
		final String sql = sb.toString();
		sb.replace(sb.indexOf("GROUP"),sb.length()," ");
		final String countSql = sb.replace(7, sb.indexOf("FROM"), "COUNT(*) ").toString();
		final List<Object> parmList = list;
		return this.getHibernateTemplate().execute(new HibernateCallback<RESULT>() {

			@SuppressWarnings("unchecked")
			@Override
			public RESULT doInHibernate(Session session) throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				for (int i = 0; i < parmList.size(); i++) {
					query.setParameter(i, parmList.get(i));
				}
				query.setFirstResult((pageBean.getPage() - 1) * pageBean.getPageSize());
				query.setMaxResults(pageBean.getPageSize());
				
				List<Object[]> resultSet = query.list();
				if (resultSet != null) {
					List<ClassesDTO> ssList = new ArrayList<ClassesDTO>();
					ClassesDTO c = null;
					for (Object[] row : resultSet) {
						c = new ClassesDTO();
						Integer code = row[0] != null ? (Integer)row[0] : null;
						String name = row[1] != null ? String.valueOf(row[1]) :null;
						Date ct = row[2] != null ? (Date)row[2] : null;
						Integer cu = row[3] != null ? (Integer)row[3] :null;
						String cn = row[4] != null ? String.valueOf(row[4]): null;
						Date startSignTime = row[5] != null ? (Date)row[5] :null;
						Date finishTime = row[6] != null ? (Date)row[6] : null;
						Date beginTime = row[7] != null ? (Date)row[7] : null;
						Date endSignTime = row[8] != null ? (Date)row[8] : null;
						Byte how = row[9] != null ? (Byte)row[9]:null;
						Boolean isFront = row[10] != null ? (Boolean)row[10] :null;
						Short num = row[11] != null ? (Short)row[11] :null;
						Integer coc = row[12] != null ?(Integer)row[12] : null;
						Boolean deleted = row[13] != null ? (Boolean)row[13] : null;
						Integer count = row[14] != null ?((BigInteger)row[14]).intValue() : null;
						
						c.setCode(code);
						c.setName(name);
						c.setCt(ct);
						c.setCu(cu);
						c.setCn(cn);
						c.setStartSignTime(startSignTime);
						c.setFinishTime(finishTime);
						c.setBeginTime(beginTime);
						c.setEndSignTime(endSignTime);
						c.setHow(how);
						c.setIsFront(isFront);
						c.setNum(num);
						c.setOc(coc);
						c.setDeleted(deleted);
						c.setCount(count);
						ssList.add(c);
					}
					pageBean.setResultList(ssList);
					SQLQuery countQuery = session.createSQLQuery(countSql);
					for (int i = 0; i < parmList.size(); i++) {
						countQuery.setParameter(i, parmList.get(i));
					}
					
					Long total = ((BigInteger) countQuery.uniqueResult()).longValue();
					pageBean.setTotal(total);
					pageBean.compute();
					return RESULT.SUCCESS;
				}
				return RESULT.ERROR;
			}
		});
	}
	public RESULT findByMap1(Integer uc, Map<String, String> map, PageBean pb) {
		if(uc == null) return null;
		List<Object> list = new ArrayList<Object>();
		List<Object> countParm = new ArrayList<Object>();
		StringBuffer temp = new StringBuffer(" FROM Classes c");
		temp.append(" JOIN UserInfo u ON u.code = c.cu");
		temp.append(" JOIN Organ o ON o.code = c.oc");
		temp.append(" JOIN UserClassesRelation ucr ON c.code = ucr.clc");
		temp.append(" WHERE c.deleted = 0 AND ucr.uc = ? AND ucr.status = 3");
		list.add(uc);
		countParm.add(uc);
		if(map != null){
			String name = map.get(Parm.NAME);
			String deleted = map.get(Parm.DELETED);
			
			if(isNotNull(name)){
				temp.append(" AND c.name LIKE ?");
				countParm.add("%" + name + "%");
				list.add("%" + name + "%");
			}
			if(isNotNull(deleted)){
				temp.append(" AND c.deleted = ?");
				countParm.add(Boolean.parseBoolean(deleted));
				list.add(Boolean.parseBoolean(deleted));
			}
		}
		StringBuffer sql = new StringBuffer("SELECT c.code, c.name, c.cu, c.ct, c.oc, c.deleted, u.realName, o.name as oname");
		StringBuffer countSql = new StringBuffer("SELECT COUNT(*)");
		
		sql.append(temp).append(" ORDER BY c.code DESC");
		countSql.append(temp);
		if (RESULT.SUCCESS == queryToPageBean(pb, sql.toString(), list, countSql.toString(), countParm))
			return packageResult(pb);
		else
			return RESULT.ERROR;
	}
	/***
	 * 封装
	 * @param pb
	 * @return
	 */
	private RESULT packageResult(PageBean pb) {
		
		Classes info = null;
		List<Classes> list = new ArrayList<Classes>();
		
		//  c.code, c.title, c.cu, c.ct, c.oc, c.deleted, u.realName, o.name
		for (Object o : pb.getResultList()) {
			
			info = new Classes();
			Object[] obj = (Object[]) o;
			
			Integer code = (null == obj[0] || Sys.NONE.equals(obj[0].toString())) ? null : (Integer) obj[0];
			String name = (null == obj[1] || Sys.NONE.equals(obj[1].toString())) ? null : (String) obj[1];
			Integer cu = (null == obj[2] || Sys.NONE.equals(obj[2].toString())) ? null : (Integer) obj[2];
			Date ct = (null == obj[3] || Sys.NONE.equals(obj[3].toString())) ? null : (Date) obj[3];
			Integer oc = (null == obj[4] || Sys.NONE.equals(obj[4].toString())) ? null : (Integer) obj[4];
			Boolean deleted = (null == obj[5] || Sys.NONE.equals(obj[5].toString())) ? null : (Boolean) obj[5];
			String cn = (null == obj[6] || Sys.NONE.equals(obj[6].toString())) ? null : (String) obj[6];
			String oname = (null == obj[7] || Sys.NONE.equals(obj[7].toString())) ? null : (String) obj[7];
			
			info.setCode(code);
			info.setName(name);
			info.setCu(cu);
			info.setCt(ct);
			info.setOc(oc);
			info.setDeleted(deleted);
			info.setCn(cn);
			info.setOn(oname);
			
			list.add(info);
		}
		pb.setResultList(list);
		return RESULT.SUCCESS;
	}
	
	
	/**
	 * 我的班级
	 * 学员
	 * uc 学员编号
	 * name 班级名称
	 */
	public List<Classes> findByMyClass(Integer uc, String name){
		if(uc == null) return null;
		List<Object> parmList = new ArrayList<Object>();
		StringBuffer sqlBuffer = new StringBuffer("SELECT c.code, c.name FROM Classes c");
		sqlBuffer.append(" JOIN UserClassesRelation ucr ON c.code = ucr.clc");
		sqlBuffer.append(" WHERE c.deleted = 0 AND ucr.uc = ? AND ucr.status = 3");
		parmList.add(uc);
		if(isNotNull(name)){
			sqlBuffer.append(" AND c.name LIKE ?");
			parmList.add("%" + name + "%");
		}
		
		final String sql = sqlBuffer.toString();
		final List<Object> parm = parmList;
		
		return this.getHibernateTemplate().execute(new HibernateCallback<List<Classes>>() {

			@Override
			public List<Classes> doInHibernate(Session session)
					throws HibernateException, SQLException {
				SQLQuery query = session.createSQLQuery(sql);
				for(int i = 0; i < parm.size(); i++){
					query.setParameter(i, parm.get(i));
				}
				
				@SuppressWarnings("unchecked")
				List<Object[]> result = query.list();
				if(result != null){
					List<Classes> list = new ArrayList<Classes>();
					Classes c = null;
					for(Object[] item : result){
						c = new Classes();
						c.setCode((Integer)item[0]);
						c.setName((String)item[1]);
						list.add(c);
					}
					return list;
				}
				return null;
			}
		});
	}
	
	public List<Classes> findMyOcAndNamelasses(Integer oc,String name){
		String hql = null;
		if(!isNotNull(name)){
			hql = "FROM Classes c WHERE c.deleted = 0 AND c.oc = ?";
			return find(hql,oc);
		}else{
		    hql = "FROM Classes c WHERE c.deleted = 0 AND c.oc = ? AND c.name LIKE ?";
			return findI(hql,oc, "%" + name + "%");
		}
	}
	//阅卷时用的查询
	public RESULT findByOc(Integer oc, Map<String, String> map, PageBean pageBean) {
		if(oc == null) return null;
		StringBuffer sql = new StringBuffer("FROM Classes c WHERE c.oc = ?");
		List<Object> list = new ArrayList<Object>();
		list.add(oc);
		if(map != null){
			String name = map.get(Parm.NAME);
			String deleted = map.get(Parm.DELETED);
			
			if(isNotNull(name)){
				sql.append(" AND c.name LIKE ?");
				list.add("%" + name + "%");
			}
			if(isNotNull(deleted)){
				sql.append(" AND c.deleted = ?");
				list.add(Boolean.parseBoolean(deleted));
			}
		}
		sql.append(" ORDER BY c.code DESC");
		return findToPageBean(sql.toString(), pageBean, list);
	}
	
	/**
	 * 更新操作
	 * @return
	 */
	public RESULT update(Classes newC){
		if(newC == null){
			return RESULT.ERROR;
		}
		Classes old = get(newC.getCode());
		if(newC.getIsFront() != null && newC.getIsFront() != old.getIsFront()){
			old.setIsFront(newC.getIsFront());
		}
		if(newC.getBeginTime() != null && newC.getBeginTime() != old.getBeginTime()){
			old.setBeginTime(newC.getBeginTime());
		}
		if(newC.getEndSignTime() != null && newC.getEndSignTime() != old.getEndSignTime()){
			old.setEndSignTime(newC.getEndSignTime());
		}
		if(newC.getFinishTime() != null && newC.getFinishTime() != old.getFinishTime()){
			old.setFinishTime(newC.getFinishTime());
		}
		if(newC.getHow() != null && newC.getHow() != old.getHow()){
			old.setHow(newC.getHow());
		}
		if(isNotNull(newC.getName()) && newC.getName() != old.getName()){
			old.setName(newC.getName());
		}
		if(newC.getNum() != null && newC.getNum() != old.getNum()){
			old.setNum(newC.getNum());
		}
		if(newC.getStartSignTime() != null && newC.getStartSignTime() != old.getStartSignTime()){
			old.setStartSignTime(newC.getStartSignTime());
		}
		return upd(old);
	}
	
	public Byte findClc(Integer code){
		StringBuffer sql = new StringBuffer("SELECT ccr.type FROM ClassesCourseRelation ccr WHERE ccr.clc = ? ");
		List<Object> parm = new ArrayList<Object>();
		parm.add(code);
		List<Object> objList = query(sql.toString(), null, 1, parm.toArray());	// 获取查询
		return (null == objList || objList.size() == 0) ? null : Byte.parseByte(objList.get(0).toString());
	}

}
